package com.visionet.waveletData.web.repository.plan.impl;

import com.visionet.waveletData.web.common.utils.DateUtils;
import com.visionet.waveletData.web.entity.plan.PlayPlan;
import com.visionet.waveletData.web.repository.plan.PlayPlanDao;
import com.visionet.waveletData.web.vo.plan.PlayPlanVo;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


@Repository
public class PlayPlanDaoImpl {
	 private final static Logger logger = LoggerFactory.getLogger(PlayPlanDao.class);

    @Resource
    private JdbcTemplate jdbcTemplate;
    
    public PageImpl<PlayPlanVo> findPagePlayPlan(PlayPlanVo playPlanVo, Pageable page){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        sql.append(" select playplan.id as id,playplan.plan_name as planname, playplan.company_id,");
        sql.append(" (select count(*) from p_plan_file where is_deleted='0'  ");
        if(null != playPlanVo.getStartTime()){
        	sql.append(" and start_time >= ? ");
        	params.add(DateUtils.getStartTimeOfDay(playPlanVo.getStartTime()));
        }
        if(null != playPlanVo.getEndTime()){
        	sql.append(" and end_time <= ? ");
        	params.add(DateUtils.getEndTimeOfDay(playPlanVo.getEndTime()));
        }
        sql.append(" and plan_id=playplan.id ) as videonum, ");
        sql.append(" (select count(*) from p_plan_group plangroup left join p_group gr on plangroup.group_id=gr.id  where plangroup.is_deleted='0' ");
        if(StringUtils.isNotBlank(playPlanVo.getGroupName())){
        	sql.append(" and gr.group_name like ? ");
        	params.add("%"+playPlanVo.getGroupName()+"%");
        }
        sql.append(" and plan_id=playplan.id) as groupnum ");
        sql.append(" from p_play_plan playplan where playplan.is_deleted='0' ");

        sql.append(" and playplan.company_id = ? ");
        params.add(playPlanVo.getCompanyId());

        if(StringUtils.isNotBlank(playPlanVo.getPlanName())){
        	sql.append(" and playplan.plan_name like ? ");
        	params.add("%"+playPlanVo.getPlanName()+"%");
        }
        sql.append(" order by playplan.create_date desc ");
        if(page == null){
            page = new PageRequest(0, 10);
        }
        int currentPage = page.getPageNumber();

        int pageSize = page.getPageSize();
        int size = jdbcTemplate.queryForInt("select count(*) from ("+sql.toString()+") as c", params.toArray());
        List<PlayPlanVo> list = jdbcTemplate.query("select t.* from ("+sql+") as t limit  "+ currentPage * pageSize+","+pageSize, params.toArray(), new RowMapper<PlayPlanVo>() {
            @Override
            public PlayPlanVo mapRow(ResultSet rs, int rowNum) throws SQLException {
            	PlayPlanVo entity = new PlayPlanVo();
                entity.setId(rs.getLong("id"));
                entity.setPlanName(rs.getString("planname"));
                entity.setVideoNum(rs.getInt("videonum"));
                entity.setGroupNum(rs.getInt("groupnum"));
                return entity;
            }
        });
        PageImpl<PlayPlanVo> playPlanPage = new PageImpl<PlayPlanVo>(list, page, size);
        return playPlanPage;
    }



    public List<PlayPlan> findAllPlanIdByUpdateDate(Date lastUpdateDate, Pageable page){
        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        sql.append(" select ppp.id,ppp.plan_name,ppp.video_num,ppp.company_id,ppp.create_date,ppp.update_date,ppp.is_deleted from p_play_plan ppp where 1=1 ");
        if(lastUpdateDate != null){
            sql.append(" and ppp.update_date > str_to_date(?,'%Y-%m-%d %H:%i:%s') ");
            params.add(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(lastUpdateDate));
        }
        if(page == null){
            page = new PageRequest(0, 10);
        }
        int currentPage = page.getPageNumber();
        int pageSize = page.getPageSize();
        List<PlayPlan> list = jdbcTemplate.query("select t.* from ("+sql+") as t limit  "+ currentPage * pageSize+","+pageSize, params.toArray(), new RowMapper<PlayPlan>() {
            @Override
            public PlayPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
                PlayPlan playPlan = new PlayPlan();
                playPlan.setId(rs.getLong("id"));
                playPlan.setPlanName(rs.getString("plan_name"));
                playPlan.setVideoNum(rs.getInt("video_num"));
                playPlan.setCompanyId(rs.getString("company_id"));
                playPlan.setCreateDate(rs.getTimestamp("create_date"));
                playPlan.setUpdateDate(rs.getTimestamp("update_date"));
                playPlan.setIs_deleted(rs.getString("is_deleted"));
                return playPlan;
            }
        });
        return list;
    }


}
